<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 4.2.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"chentianming11.github.io","root":"/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":true,"show_result":true,"style":"default"},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":{"enable":true,"onlypost":false,"loadingImg":"/uploads/loading.gif"},"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="最近项目中不少表的数据量越来越大，并且导致了一些数据库的性能问题。因此想借助一些分库分表的中间件，实现自动化分库分表实现。调研下来，发现Sharding-JDBC目前成熟度最高并且应用最广的Java分库分表的客户端组件。本文主要介绍一些Sharding-JDBC核心概念以及生产环境下的实战指南，旨在帮助组内成员快速了解Sharding-JDBC并且能够快速将其使用起来。Sharding-JDBC官">
<meta property="og:type" content="article">
<meta property="og:title" content="数据量大了一定要分表，分库分表组件Sharding-JDBC入门与项目实战">
<meta property="og:url" content="https://chentianming11.github.io/posts/2363983740/index.html">
<meta property="og:site_name" content="伍六七">
<meta property="og:description" content="最近项目中不少表的数据量越来越大，并且导致了一些数据库的性能问题。因此想借助一些分库分表的中间件，实现自动化分库分表实现。调研下来，发现Sharding-JDBC目前成熟度最高并且应用最广的Java分库分表的客户端组件。本文主要介绍一些Sharding-JDBC核心概念以及生产环境下的实战指南，旨在帮助组内成员快速了解Sharding-JDBC并且能够快速将其使用起来。Sharding-JDBC官">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://shardingsphere.apache.org/document/current/img/sharding/sharding_architecture_cn.png">
<meta property="og:image" content="https://chentianming11.github.io/images/month-increse.png">
<meta property="article:published_time" content="2020-06-06T06:36:43.000Z">
<meta property="article:modified_time" content="2020-08-05T00:42:44.954Z">
<meta property="article:author" content="陈添明">
<meta property="article:tag" content="sharing-jdbc">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://shardingsphere.apache.org/document/current/img/sharding/sharding_architecture_cn.png">

<link rel="canonical" href="https://chentianming11.github.io/posts/2363983740/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>数据量大了一定要分表，分库分表组件Sharding-JDBC入门与项目实战 | 伍六七</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">伍六七</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">一起学Java</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">6</span></a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">3</span></a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">17</span></a>

  </li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div id="search-result">
  <div id="no-result">
    <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
  </div>
</div>

    </div>
  </div>

</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>

  <a href="https://github.com/chentianming11" class="github-corner" title="Follow me on GitHub" aria-label="Follow me on GitHub" rel="noopener" target="_blank"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://chentianming11.github.io/posts/2363983740/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.jpg">
      <meta itemprop="name" content="陈添明">
      <meta itemprop="description" content="一个爱看国漫的程序猿">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="伍六七">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          数据量大了一定要分表，分库分表组件Sharding-JDBC入门与项目实战
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2020-06-06 14:36:43" itemprop="dateCreated datePublished" datetime="2020-06-06T14:36:43+08:00">2020-06-06</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2020-08-05 08:42:44" itemprop="dateModified" datetime="2020-08-05T08:42:44+08:00">2020-08-05</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a>
                </span>
            </span>

          
  
  <span class="post-meta-item">
    
      <span class="post-meta-item-icon">
        <i class="far fa-comment"></i>
      </span>
      <span class="post-meta-item-text">Valine：</span>
    
    <a title="valine" href="/posts/2363983740/#valine-comments" itemprop="discussionUrl">
      <span class="post-comments-count valine-comment-count" data-xid="/posts/2363983740/" itemprop="commentCount"></span>
    </a>
  </span>
  
  <br>
            <span class="post-meta-item" title="本文字数">
              <span class="post-meta-item-icon">
                <i class="far fa-file-word"></i>
              </span>
                <span class="post-meta-item-text">本文字数：</span>
              <span>11k</span>
            </span>
            <span class="post-meta-item" title="阅读时长">
              <span class="post-meta-item-icon">
                <i class="far fa-clock"></i>
              </span>
                <span class="post-meta-item-text">阅读时长 &asymp;</span>
              <span>27 分钟</span>
            </span>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <p>最近项目中不少表的数据量越来越大，并且导致了一些数据库的性能问题。因此想借助一些分库分表的中间件，实现自动化分库分表实现。调研下来，发现<code>Sharding-JDBC</code>目前成熟度最高并且应用最广的<code>Java分库分表的客户端组件</code>。本文主要介绍一些Sharding-JDBC核心概念以及生产环境下的实战指南，旨在帮助组内成员快速了解Sharding-JDBC并且能够快速将其使用起来。<a href="https://shardingsphere.apache.org/document/current/cn/overview/" target="_blank" rel="noopener">Sharding-JDBC官方文档</a></p>
<a id="more"></a>

<h2 id="核心概念"><a href="#核心概念" class="headerlink" title="核心概念"></a>核心概念</h2><p>在使用<code>Sharding-JDBC</code>之前，一定是先理解清楚下面几个核心概念。</p>
<h3 id="逻辑表"><a href="#逻辑表" class="headerlink" title="逻辑表"></a>逻辑表</h3><p>水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是<code>t_order_0</code>到<code>t_order_9</code>，他们的逻辑表名为<code>t_order</code>。</p>
<h3 id="真实表"><a href="#真实表" class="headerlink" title="真实表"></a>真实表</h3><p>在分片的数据库中真实存在的物理表。即上个示例中的<code>t_order_0</code>到<code>t_order_9</code>。</p>
<h3 id="数据节点"><a href="#数据节点" class="headerlink" title="数据节点"></a>数据节点</h3><p>数据分片的最小单元。由数据源名称和数据表组成，例：<code>ds_0.t_order_0</code>。</p>
<h3 id="绑定表"><a href="#绑定表" class="headerlink" title="绑定表"></a>绑定表</h3><p>指分片规则一致的主表和子表。例如：<code>t_order</code>表和<code>t_order_item</code>表，均按照<code>order_id</code>分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明,如果SQL为：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order o <span class="keyword">JOIN</span> t_order_item i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br></pre></td></tr></table></figure>

<p>假设<code>t_order</code>和<code>t_order_item</code>对应的真实表各有2个，那么真实表就有<code>t_order_0</code>、<code>t_order_1</code>、<code>t_order_item_0</code>、<code>t_order_item_1</code>。在不配置绑定表关系时，假设分片键<code>order_id</code>将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_0 o <span class="keyword">JOIN</span> t_order_item_0 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_0 o <span class="keyword">JOIN</span> t_order_item_1 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_1 o <span class="keyword">JOIN</span> t_order_item_0 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_1 o <span class="keyword">JOIN</span> t_order_item_1 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br></pre></td></tr></table></figure>

<p>在配置绑定表关系后，路由的SQL应该为2条：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_0 o <span class="keyword">JOIN</span> t_order_item_0 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br><span class="line"><span class="keyword">SELECT</span> i.* <span class="keyword">FROM</span> t_order_1 o <span class="keyword">JOIN</span> t_order_item_1 i <span class="keyword">ON</span> o.order_id=i.order_id <span class="keyword">WHERE</span> o.order_id <span class="keyword">in</span> (<span class="number">10</span>, <span class="number">11</span>);</span><br></pre></td></tr></table></figure>

<h3 id="广播表"><a href="#广播表" class="headerlink" title="广播表"></a>广播表</h3><p>指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。</p>
<h2 id="数据分片"><a href="#数据分片" class="headerlink" title="数据分片"></a>数据分片</h2><h3 id="分片键"><a href="#分片键" class="headerlink" title="分片键"></a>分片键</h3><p>用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL 中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，Sharding-JDBC 也支持根据多个字段进行分片。</p>
<h3 id="分片算法"><a href="#分片算法" class="headerlink" title="分片算法"></a>分片算法</h3><p>通过分片算法将数据分片，支持通过<code>=、&gt;=、&lt;=、&gt;、&lt;、BETWEEN和IN</code>分片。 分片算法需要应用方开发者自行实现，可实现的灵活度非常高。</p>
<p>目前提供4种分片算法。 由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。</p>
<h4 id="精确分片算法"><a href="#精确分片算法" class="headerlink" title="精确分片算法"></a>精确分片算法</h4><p>对应 <code>PreciseShardingAlgorithm</code>，<strong>用于处理使用单一键作为分片键的 = 与 IN 进行分片的场景</strong>。需要配合 <code>StandardShardingStrategy</code> 使用。</p>
<h4 id="范围分片算法"><a href="#范围分片算法" class="headerlink" title="范围分片算法"></a>范围分片算法</h4><p>对应 <code>RangeShardingAlgorithm</code>，<strong>用于处理使用单一键作为分片键的 BETWEEN AND、&gt;、&lt;、&gt;=、&lt;=进行分片的场景</strong>。需要配合 StandardShardingStrategy 使用。</p>
<h4 id="复合分片算法"><a href="#复合分片算法" class="headerlink" title="复合分片算法"></a>复合分片算法</h4><p>对应 <code>ComplexKeysShardingAlgorithm</code>，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合 <code>ComplexShardingStrategy</code> 使用。</p>
<h4 id="Hint分片算法"><a href="#Hint分片算法" class="headerlink" title="Hint分片算法"></a>Hint分片算法</h4><p>对应 <code>HintShardingAlgorithm</code>，<strong>用于处理通过Hint指定分片值而非从SQL中提取分片值的场景</strong>。需要配合 <code>HintShardingStrategy</code> 使用。</p>
<h3 id="分片策略"><a href="#分片策略" class="headerlink" title="分片策略"></a>分片策略</h3><p>包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供 5 种分片策略。</p>
<h3 id="标准分片策略"><a href="#标准分片策略" class="headerlink" title="标准分片策略"></a>标准分片策略</h3><p>对应 <code>StandardShardingStrategy</code>。提供对 SQ L语句中的 <code>=, &gt;, &lt;, &gt;=, &lt;=, IN 和 BETWEEN AND</code> 的分片操作支持。 <code>StandardShardingStrategy</code> 只支持单分片键，提供 <code>PreciseShardingAlgorithm</code> 和 <code>RangeShardingAlgorithm</code> 两个分片算法。 <strong><code>PreciseShardingAlgorithm</code> 是必选的</strong>，用于处理 = 和 IN 的分片。 <code>RangeShardingAlgorithm</code> 是可选的，用于处理 <code>BETWEEN AND, &gt;, &lt;, &gt;=, &lt;=</code>分片，如果不配置 RangeShardingAlgorithm，SQL 中的 BETWEEN AND 将按照全库路由处理。</p>
<h3 id="复合分片策略"><a href="#复合分片策略" class="headerlink" title="复合分片策略"></a>复合分片策略</h3><p>对应 <code>ComplexShardingStrategy</code>。复合分片策略。提供对 SQL 语句中的 <code>=, &gt;, &lt;, &gt;=, &lt;=, IN 和 BETWEEN AND</code> 的分片操作支持。 <strong><code>ComplexShardingStrategy</code> 支持多分片键</strong>，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。</p>
<h3 id="行表达式分片策略"><a href="#行表达式分片策略" class="headerlink" title="行表达式分片策略"></a>行表达式分片策略</h3><p>对应 <code>InlineShardingStrategy</code>。使用 Groovy 的表达式，提供对 SQL 语句中的 <code>= 和 IN</code>的分片操作支持，只支持单分片键。 对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: <code>t_user_$-&gt;{u_id % 8}</code> 表示 t_user 表根据 u_id 模 8，而分成 8 张表，表名称为 <code>t_user_0</code> 到 <code>t_user_7</code>。 <strong>可以认为是精确分片算法的简易实现</strong></p>
<h3 id="Hint分片策略"><a href="#Hint分片策略" class="headerlink" title="Hint分片策略"></a>Hint分片策略</h3><p>对应 HintShardingStrategy。通过 Hint 指定分片值而非从 SQL 中提取分片值的方式进行分片的策略。</p>
<h3 id="分布式主键"><a href="#分布式主键" class="headerlink" title="分布式主键"></a>分布式主键</h3><p>用于在分布式环境下，生成全局唯一的id。Sharding-JDBC 提供了内置的分布式主键生成器，例如 <code>UUID</code>、<code>SNOWFLAKE</code>。还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器。<strong>为了保证数据库性能，主键id还必须趋势递增，避免造成频繁的数据页面分裂。</strong></p>
<h2 id="读写分离"><a href="#读写分离" class="headerlink" title="读写分离"></a>读写分离</h2><p>提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。</p>
<ul>
<li>同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性</li>
<li>基于Hint的强制主库路由。</li>
<li>主从模型中，事务中读写均用主库。</li>
</ul>
<h2 id="执行流程"><a href="#执行流程" class="headerlink" title="执行流程"></a>执行流程</h2><p>Sharding-JDBC 的原理总结起来很简单: 核心由 <code>SQL解析 =&gt; 执行器优化 =&gt; SQL路由 =&gt; SQL改写 =&gt; SQL执行 =&gt; 结果归并</code>的流程组成。<br><img data-src="https://shardingsphere.apache.org/document/current/img/sharding/sharding_architecture_cn.png" alt="Sharding-JDBC执行流程"></p>
<h2 id="项目实战"><a href="#项目实战" class="headerlink" title="项目实战"></a>项目实战</h2><p>spring-boot项目实战</p>
<h3 id="引入依赖"><a href="#引入依赖" class="headerlink" title="引入依赖"></a>引入依赖</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.apache.shardingsphere<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>sharding-jdbc-spring-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">version</span>&gt;</span>4.0.1<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h3 id="数据源配置"><a href="#数据源配置" class="headerlink" title="数据源配置"></a>数据源配置</h3><p>如果使用<code>sharding-jdbc-spring-boot-starter</code>, 并且数据源以及数据分片都使用shardingsphere进行配置，对应的数据源会自动创建并注入到spring容器中。</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">spring.shardingsphere.datasource.names</span>=<span class="string">ds0,ds1</span></span><br><span class="line"></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds0.type</span>=<span class="string">org.apache.commons.dbcp.BasicDataSource</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds0.driver-class-name</span>=<span class="string">com.mysql.jdbc.Driver</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds0.url</span>=<span class="string">jdbc:mysql://localhost:3306/ds0</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds0.username</span>=<span class="string">root</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds0.password</span>=<span class="string"></span></span><br><span class="line"></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds1.type</span>=<span class="string">org.apache.commons.dbcp.BasicDataSource</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds1.driver-class-name</span>=<span class="string">com.mysql.jdbc.Driver</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds1.url</span>=<span class="string">jdbc:mysql://localhost:3306/ds1</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds1.username</span>=<span class="string">root</span></span><br><span class="line"><span class="meta">spring.shardingsphere.datasource.ds1.password</span>=<span class="string"></span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 其它分片配置</span></span><br></pre></td></tr></table></figure>

<p>但是在我们已有的项目中，数据源配置是单独的。<strong>因此要禁用<code>sharding-jdbc-spring-boot-starter</code>里面的自动装配，而是参考源码自己重写数据源配置</strong>。需要在启动类上加上<code>@SpringBootApplication(exclude = {org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration.class})</code>来排除。然后自定义配置类来装配<code>DataSource</code>。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Configuration</span></span><br><span class="line"><span class="meta">@Slf</span>4j</span><br><span class="line"><span class="meta">@EnableConfigurationProperties</span>(&#123;</span><br><span class="line">        SpringBootShardingRuleConfigurationProperties<span class="class">.<span class="keyword">class</span>,</span></span><br><span class="line"><span class="class">        <span class="title">SpringBootMasterSlaveRuleConfigurationProperties</span>.<span class="title">class</span>, <span class="title">SpringBootEncryptRuleConfigurationProperties</span>.<span class="title">class</span>, <span class="title">SpringBootPropertiesConfigurationProperties</span>.<span class="title">class</span>&#125;)</span></span><br><span class="line"><span class="class">@<span class="title">AutoConfigureBefore</span>(<span class="title">DataSourceConfiguration</span>.<span class="title">class</span>)</span></span><br><span class="line"><span class="class"><span class="title">public</span> <span class="title">class</span> <span class="title">DataSourceConfig</span> <span class="keyword">implements</span> <span class="title">ApplicationContextAware</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> SpringBootShardingRuleConfigurationProperties shardingRule;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> SpringBootPropertiesConfigurationProperties props;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> ApplicationContext applicationContext;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Bean</span>(<span class="string">"shardingDataSource"</span>)</span><br><span class="line">    <span class="meta">@Conditional</span>(ShardingRuleCondition<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class">    <span class="title">public</span> <span class="title">DataSource</span> <span class="title">shardingDataSource</span>() <span class="title">throws</span> <span class="title">SQLException</span> </span>&#123;</span><br><span class="line">        <span class="comment">// 获取其它方式配置的数据源</span></span><br><span class="line">        Map&lt;String, DruidDataSourceWrapper&gt; beans = applicationContext.getBeansOfType(DruidDataSourceWrapper<span class="class">.<span class="keyword">class</span>)</span>;</span><br><span class="line">        Map&lt;String, DataSource&gt; dataSourceMap = <span class="keyword">new</span> HashMap&lt;&gt;(<span class="number">4</span>);</span><br><span class="line">        beans.forEach(dataSourceMap::put);</span><br><span class="line">        <span class="comment">// 创建shardingDataSource</span></span><br><span class="line">        <span class="keyword">return</span> ShardingDataSourceFactory.createDataSource(dataSourceMap, <span class="keyword">new</span> ShardingRuleConfigurationYamlSwapper().swap(shardingRule), props.getProps());</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Bean</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> SqlSessionFactory <span class="title">sqlSessionFactory</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">        SqlSessionFactoryBean sqlSessionFactoryBean = <span class="keyword">new</span> SqlSessionFactoryBean();</span><br><span class="line">        <span class="comment">// 将shardingDataSource设置到SqlSessionFactory中</span></span><br><span class="line">        sqlSessionFactoryBean.setDataSource(shardingDataSource());</span><br><span class="line">        <span class="comment">// 其它设置</span></span><br><span class="line">        <span class="keyword">return</span> sqlSessionFactoryBean.getObject();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h3 id="分布式id生成器配置"><a href="#分布式id生成器配置" class="headerlink" title="分布式id生成器配置"></a>分布式id生成器配置</h3><p>Sharding-JDBC提供了<code>UUID</code>、<code>SNOWFLAKE</code>生成器，还支持用户实现自定义id生成器。比如可以实现了type为<code>SEQ</code>的分布式id生成器，调用统一的<code>分布式id服务</code>获取id。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">SeqShardingKeyGenerator</span> <span class="keyword">implements</span> <span class="title">ShardingKeyGenerator</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> Properties properties = <span class="keyword">new</span> Properties();</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> String <span class="title">getType</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> <span class="string">"SEQ"</span>;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">synchronized</span> Comparable&lt;?&gt; generateKey() &#123;</span><br><span class="line">       <span class="comment">// 获取分布式id逻辑</span></span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p><strong>由于扩展<code>ShardingKeyGenerator</code>是通过JDK的<code>serviceloader</code>的SPI机制实现的，因此还需要在<code>resources/META-INF/services</code>目录下配置<code>org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator</code>文件。</strong> 文件内容就是<code>SeqShardingKeyGenerator</code>类的全路径名。这样使用的时候，指定分布式主键生成器的type为<code>SEQ</code>就好了。</p>
<p>至此，Sharding-JDBC就整合进spring-boot项目中了，后面就可以进行数据分片相关的配置了。</p>
<h3 id="数据分片实战"><a href="#数据分片实战" class="headerlink" title="数据分片实战"></a>数据分片实战</h3><p>如果项目初期就能预估出表的数据量级，当然可以一开始就按照这个预估值进行分库分表处理。但是大多数情况下，我们一开始并不能准备预估出数量级。这时候通常的做法是：</p>
<ol>
<li>线上数据某张表查询性能开始下降，排查下来是因为数据量过大导致的。</li>
<li>根据历史数据量预估出未来的数据量级，并结合具体业务场景确定分库分表策略。</li>
<li>自动分库分表代码实现。</li>
</ol>
<p>下面就以一个具体事例，阐述具体数据分片实战。比如有张表数据结构如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`hc_question_reply_record`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'自增ID'</span>,</span><br><span class="line">  <span class="string">`reply_text`</span> <span class="built_in">varchar</span>(<span class="number">500</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">''</span> <span class="keyword">COMMENT</span> <span class="string">'回复内容'</span>,</span><br><span class="line">  <span class="string">`reply_wheel_time`</span> datetime <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="keyword">CURRENT_TIMESTAMP</span> <span class="keyword">COMMENT</span> <span class="string">'回复时间'</span>,</span><br><span class="line"></span><br><span class="line">  <span class="string">`ctime`</span> datetime <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="keyword">CURRENT_TIMESTAMP</span> <span class="keyword">COMMENT</span> <span class="string">'创建时间'</span>,</span><br><span class="line">  <span class="string">`mtime`</span> datetime <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="keyword">CURRENT_TIMESTAMP</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CURRENT_TIMESTAMP</span> <span class="keyword">COMMENT</span> <span class="string">'更新时间'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>),</span><br><span class="line">  <span class="keyword">INDEX</span> <span class="string">`idx_reply_wheel_time`</span> (<span class="string">`reply_wheel_time`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_unicode_ci</span><br><span class="line">  <span class="keyword">COMMENT</span>=<span class="string">'回复明细记录'</span>;</span><br></pre></td></tr></table></figure>

<h4 id="分片方案确定"><a href="#分片方案确定" class="headerlink" title="分片方案确定"></a>分片方案确定</h4><p>先查询目前目标表月新增趋势：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">count</span>(*), <span class="keyword">date_format</span>(ctime, <span class="string">'%Y-%m'</span>) <span class="keyword">AS</span> <span class="string">`日期`</span></span><br><span class="line"><span class="keyword">FROM</span> hc_question_reply_record</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">date_format</span>(ctime, <span class="string">'%Y-%m'</span>);</span><br></pre></td></tr></table></figure>

<p><img data-src="https://chentianming11.github.io/images/month-increse.png" alt="月新增趋势"></p>
<p>目前月新增在180w左右，预估未来达到300w(基本以2倍计算)以上。期望单表数据量不超过1000w，可使用<code>reply_wheel_time</code>作为分片键按季度归档。</p>
<h4 id="分片配置"><a href="#分片配置" class="headerlink" title="分片配置"></a>分片配置</h4><figure class="highlight yaml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">spring:</span></span><br><span class="line">  <span class="comment"># sharing-jdbc配置</span></span><br><span class="line">  <span class="attr">shardingsphere:</span></span><br><span class="line">    <span class="comment"># 数据源名称</span></span><br><span class="line">    <span class="attr">datasource:</span></span><br><span class="line">      <span class="attr">names:</span> <span class="string">defaultDataSource,slaveDataSource</span></span><br><span class="line">    <span class="attr">sharding:</span></span><br><span class="line">      <span class="comment"># 主从节点配置</span></span><br><span class="line">      <span class="attr">master-slave-rules:</span></span><br><span class="line">        <span class="attr">defaultDataSource:</span></span><br><span class="line">          <span class="comment"># maser数据源</span></span><br><span class="line">          <span class="attr">master-data-source-name:</span> <span class="string">defaultDataSource</span></span><br><span class="line">          <span class="comment"># slave数据源</span></span><br><span class="line">          <span class="attr">slave-data-source-names:</span> <span class="string">slaveDataSource</span></span><br><span class="line">      <span class="attr">tables:</span></span><br><span class="line">        <span class="comment"># hc_question_reply_record 分库分表配置</span></span><br><span class="line">        <span class="attr">hc_question_reply_record:</span></span><br><span class="line">          <span class="comment"># 真实数据节点  hc_question_reply_record_2020_q1</span></span><br><span class="line">          <span class="attr">actual-data-nodes:</span> <span class="string">defaultDataSource.hc_question_reply_record_$-&gt;&#123;2020..2025&#125;_q$-&gt;&#123;1..4&#125;</span></span><br><span class="line">          <span class="comment"># 表分片策略</span></span><br><span class="line">          <span class="attr">table-strategy:</span></span><br><span class="line">            <span class="attr">standard:</span></span><br><span class="line">              <span class="comment"># 分片键</span></span><br><span class="line">              <span class="attr">sharding-column:</span> <span class="string">reply_wheel_time</span></span><br><span class="line">              <span class="comment"># 精确分片算法 全路径名</span></span><br><span class="line">              <span class="attr">preciseAlgorithmClassName:</span> <span class="string">com.xx.QuestionRecordPreciseShardingAlgorithm</span></span><br><span class="line">              <span class="comment"># 范围分片算法，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器</span></span><br><span class="line">              <span class="attr">rangeAlgorithmClassName:</span> <span class="string">com.xx.QuestionRecordRangeShardingAlgorithm</span></span><br><span class="line"></span><br><span class="line">      <span class="comment"># 默认分布式id生成器</span></span><br><span class="line">      <span class="attr">default-key-generator:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">SEQ</span></span><br><span class="line">        <span class="attr">column:</span> <span class="string">id</span></span><br></pre></td></tr></table></figure>

<h4 id="分片算法实现"><a href="#分片算法实现" class="headerlink" title="分片算法实现"></a>分片算法实现</h4><ul>
<li><p>精确分片算法：<code>QuestionRecordPreciseShardingAlgorithm</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">QuestionRecordPreciseShardingAlgorithm</span> <span class="keyword">implements</span> <span class="title">PreciseShardingAlgorithm</span>&lt;<span class="title">Date</span>&gt; </span>&#123;</span><br><span class="line">  <span class="comment">/**</span></span><br><span class="line"><span class="comment">   * Sharding.</span></span><br><span class="line"><span class="comment">   *</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@param</span> availableTargetNames available data sources or tables's names</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@param</span> shardingValue        sharding value</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@return</span> sharding result for data source or table's name</span></span><br><span class="line"><span class="comment">   */</span></span><br><span class="line">  <span class="meta">@Override</span></span><br><span class="line">  <span class="function"><span class="keyword">public</span> String <span class="title">doSharding</span><span class="params">(Collection&lt;String&gt; availableTargetNames, PreciseShardingValue&lt;Date&gt; shardingValue)</span> </span>&#123;</span><br><span class="line">      <span class="keyword">return</span> ShardingUtils.quarterPreciseSharding(availableTargetNames, shardingValue);</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
</li>
<li><p>范围分片算法：<code>QuestionRecordRangeShardingAlgorithm</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">QuestionRecordRangeShardingAlgorithm</span> <span class="keyword">implements</span> <span class="title">RangeShardingAlgorithm</span>&lt;<span class="title">Date</span>&gt; </span>&#123;</span><br><span class="line"></span><br><span class="line">  <span class="comment">/**</span></span><br><span class="line"><span class="comment">   * Sharding.</span></span><br><span class="line"><span class="comment">   *</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@param</span> availableTargetNames available data sources or tables's names</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@param</span> shardingValue        sharding value</span></span><br><span class="line"><span class="comment">   * <span class="doctag">@return</span> sharding results for data sources or tables's names</span></span><br><span class="line"><span class="comment">   */</span></span><br><span class="line">  <span class="meta">@Override</span></span><br><span class="line">  <span class="function"><span class="keyword">public</span> Collection&lt;String&gt; <span class="title">doSharding</span><span class="params">(Collection&lt;String&gt; availableTargetNames, RangeShardingValue&lt;Date&gt; shardingValue)</span> </span>&#123;</span><br><span class="line">      <span class="keyword">return</span> ShardingUtils.quarterRangeSharding(availableTargetNames, shardingValue);</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
</li>
<li><p>具体分片实现逻辑：<code>ShardingUtils</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@UtilityClass</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">ShardingUtils</span> </span>&#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> String QUARTER_SHARDING_PATTERN = <span class="string">"%s_%d_q%d"</span>;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">    * logicTableName_&#123;year&#125;_q&#123;quarter&#125;</span></span><br><span class="line"><span class="comment">    * 按季度范围分片</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@param</span> availableTargetNames 可用的真实表集合</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@param</span> shardingValue 分片值</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@return</span></span></span><br><span class="line"><span class="comment">    */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> Collection&lt;String&gt; <span class="title">quarterRangeSharding</span><span class="params">(Collection&lt;String&gt; availableTargetNames, RangeShardingValue&lt;Date&gt; shardingValue)</span> </span>&#123;</span><br><span class="line">        <span class="comment">// 这里就是根据范围查询条件，筛选出匹配的真实表集合</span></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">    * logicTableName_&#123;year&#125;_q&#123;quarter&#125;</span></span><br><span class="line"><span class="comment">    * 按季度精确分片</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@param</span> availableTargetNames 可用的真实表集合</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@param</span> shardingValue 分片值</span></span><br><span class="line"><span class="comment">    * <span class="doctag">@return</span></span></span><br><span class="line"><span class="comment">    */</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> String <span class="title">quarterPreciseSharding</span><span class="params">(Collection&lt;String&gt; availableTargetNames, PreciseShardingValue&lt;Date&gt; shardingValue)</span> </span>&#123;</span><br><span class="line">        <span class="comment">// 这里就是根据等值查询条件，计算出匹配的真实表</span></span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

</li>
</ul>
<p>到这里，针对<code>hc_question_reply_record</code>表，使用<code>reply_wheel_time</code>作为分片键，按照季度分片的处理就完成了。还有一点要注意的就是，<strong>分库分表之后，查询的时候最好都带上分片键作为查询条件</strong>，否则就会使用全库路由，性能很低。 还有就是<code>Sharing-JDBC</code>对<code>mysql</code>的全文索引支持的不是很好，项目有使用到的地方也要注意一下。总结来说整个过程还是比较简单的，后续碰到其它业务场景，相信大家按照这个思路肯定都能解决的。</p>
<blockquote>
<p>原创不易，觉得文章写得不错的小伙伴，点个赞👍 鼓励一下吧~</p>
</blockquote>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/sharing-jdbc/" rel="tag"># sharing-jdbc</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item"></div>
      <div class="post-nav-item">
    <a href="/posts/2624407690/" rel="next" title="mysql进阶知识点，启动项、系统变量、字符集介绍！">
      mysql进阶知识点，启动项、系统变量、字符集介绍！ <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          
    <div class="comments" id="valine-comments"></div>

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#核心概念"><span class="nav-number">1.</span> <span class="nav-text">核心概念</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#逻辑表"><span class="nav-number">1.1.</span> <span class="nav-text">逻辑表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#真实表"><span class="nav-number">1.2.</span> <span class="nav-text">真实表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据节点"><span class="nav-number">1.3.</span> <span class="nav-text">数据节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#绑定表"><span class="nav-number">1.4.</span> <span class="nav-text">绑定表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#广播表"><span class="nav-number">1.5.</span> <span class="nav-text">广播表</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数据分片"><span class="nav-number">2.</span> <span class="nav-text">数据分片</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#分片键"><span class="nav-number">2.1.</span> <span class="nav-text">分片键</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分片算法"><span class="nav-number">2.2.</span> <span class="nav-text">分片算法</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#精确分片算法"><span class="nav-number">2.2.1.</span> <span class="nav-text">精确分片算法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#范围分片算法"><span class="nav-number">2.2.2.</span> <span class="nav-text">范围分片算法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#复合分片算法"><span class="nav-number">2.2.3.</span> <span class="nav-text">复合分片算法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#Hint分片算法"><span class="nav-number">2.2.4.</span> <span class="nav-text">Hint分片算法</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分片策略"><span class="nav-number">2.3.</span> <span class="nav-text">分片策略</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#标准分片策略"><span class="nav-number">2.4.</span> <span class="nav-text">标准分片策略</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#复合分片策略"><span class="nav-number">2.5.</span> <span class="nav-text">复合分片策略</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#行表达式分片策略"><span class="nav-number">2.6.</span> <span class="nav-text">行表达式分片策略</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Hint分片策略"><span class="nav-number">2.7.</span> <span class="nav-text">Hint分片策略</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分布式主键"><span class="nav-number">2.8.</span> <span class="nav-text">分布式主键</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#读写分离"><span class="nav-number">3.</span> <span class="nav-text">读写分离</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#执行流程"><span class="nav-number">4.</span> <span class="nav-text">执行流程</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#项目实战"><span class="nav-number">5.</span> <span class="nav-text">项目实战</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#引入依赖"><span class="nav-number">5.1.</span> <span class="nav-text">引入依赖</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据源配置"><span class="nav-number">5.2.</span> <span class="nav-text">数据源配置</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分布式id生成器配置"><span class="nav-number">5.3.</span> <span class="nav-text">分布式id生成器配置</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据分片实战"><span class="nav-number">5.4.</span> <span class="nav-text">数据分片实战</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#分片方案确定"><span class="nav-number">5.4.1.</span> <span class="nav-text">分片方案确定</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#分片配置"><span class="nav-number">5.4.2.</span> <span class="nav-text">分片配置</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#分片算法实现"><span class="nav-number">5.4.3.</span> <span class="nav-text">分片算法实现</span></a></li></ol></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="陈添明"
      src="/images/avatar.jpg">
  <p class="site-author-name" itemprop="name">陈添明</p>
  <div class="site-description" itemprop="description">一个爱看国漫的程序猿</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">17</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">3</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">6</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">陈添明</span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-chart-area"></i>
    </span>
    <span title="站点总字数">156k</span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
    <span title="站点阅读时长">6:30</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

        








      </div>
    </footer>
  </div>

  
  <script size="300" alpha="0.6" zIndex="-1" src="//cdn.jsdelivr.net/gh/theme-next/theme-next-canvas-ribbon@1/canvas-ribbon.js"></script>
  <script src="/lib/anime.min.js"></script>
  <script src="//cdn.jsdelivr.net/gh/theme-next/theme-next-pjax@0/pjax.min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/lozad@1/dist/lozad.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>

  <script>
var pjax = new Pjax({
  selectors: [
    'head title',
    '#page-configurations',
    '.content-wrap',
    '.post-toc-wrap',
    '.languages',
    '#pjax'
  ],
  switches: {
    '.post-toc-wrap': Pjax.switches.innerHTML
  },
  analytics: false,
  cacheBust: false,
  scrollTo : !CONFIG.bookmark.enable
});

window.addEventListener('pjax:success', () => {
  document.querySelectorAll('script[data-pjax], script#page-configurations, #pjax script').forEach(element => {
    var code = element.text || element.textContent || element.innerHTML || '';
    var parent = element.parentNode;
    parent.removeChild(element);
    var script = document.createElement('script');
    if (element.id) {
      script.id = element.id;
    }
    if (element.className) {
      script.className = element.className;
    }
    if (element.type) {
      script.type = element.type;
    }
    if (element.src) {
      script.src = element.src;
      // Force synchronous loading of peripheral JS.
      script.async = false;
    }
    if (element.dataset.pjax !== undefined) {
      script.dataset.pjax = '';
    }
    if (code !== '') {
      script.appendChild(document.createTextNode(code));
    }
    parent.appendChild(script);
  });
  NexT.boot.refresh();
  // Define Motion Sequence & Bootstrap Motion.
  if (CONFIG.motion.enable) {
    NexT.motion.integrator
      .init()
      .add(NexT.motion.middleWares.subMenu)
      .add(NexT.motion.middleWares.postList)
      .bootstrap();
  }
  NexT.utils.updateSidebarPosition();
});
</script>




  




  
<script src="/js/local-search.js"></script>













    <div id="pjax">
  

  
  <script src="//cdn.jsdelivr.net/npm/quicklink@1/dist/quicklink.umd.js"></script>
  <script>
      window.addEventListener('load', () => {
      quicklink({
        timeout : 3000,
        priority: true,
        ignores : [uri => uri.includes('#'),uri => uri === 'https://chentianming11.github.io/posts/2363983740/',]
      });
      });
  </script>


<script>
NexT.utils.loadComments(document.querySelector('#valine-comments'), () => {
  NexT.utils.getScript('//unpkg.com/valine/dist/Valine.min.js', () => {
    var GUEST = ['nick', 'mail', 'link'];
    var guest = 'nick,mail';
    guest = guest.split(',').filter(item => {
      return GUEST.includes(item);
    });
    new Valine({
      el         : '#valine-comments',
      verify     : false,
      notify     : true,
      appId      : 'SoPxUkme2aynTULaNuIc5TIq-gzGzoHsz',
      appKey     : '4TDP7SMCbgDKTOGGUtpIeA0H',
      placeholder: "留下点足迹吧~",
      avatar     : 'mm',
      meta       : guest,
      pageSize   : '10' || 10,
      visitor    : false,
      lang       : '' || 'zh-cn',
      path       : location.pathname,
      recordIP   : false,
      serverURLs : ''
    });
  }, window.Valine);
});
</script>

    </div>
</body>
</html>
